﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;

namespace WorkNodeFramework
{
    public class SqlTableProvider:TableProvider
    {
        public SqlTableProvider(DataProvider provider)
            : base(provider)
        { }

        public override SqlDbType GetSqlDbType(string dbTypeName)
        {
            if (string.IsNullOrEmpty(dbTypeName))
                throw new ArgumentNullException("dbTypeName");
            string name = dbTypeName.ToLower();
            switch (name)
            {
                case "uniqueidentifier":
                    return SqlDbType.UniqueIdentifier;
                case "nvarchar":
                    return SqlDbType.NVarChar;
                case "varchar":
                    return SqlDbType.VarChar;
                case "char":
                    return SqlDbType.Char;
                case "int":
                    return SqlDbType.Int;
                case "bigint":
                    return SqlDbType.BigInt;
                case "bit":
                    return SqlDbType.Bit;
                case "datetime":
                    return SqlDbType.DateTime;
                case "decimal":
                    return SqlDbType.Decimal;
                case "float":
                    return SqlDbType.Float;
                case "text":
                    return SqlDbType.Text;
                case "ntext":
                    return SqlDbType.NText;
                case "binary":
                    return SqlDbType.Binary;
                case "varbinary":
                    return SqlDbType.VarBinary;
                case "image":
                    return SqlDbType.Image;
            }

            throw new NotSupportedException();
        }

        public override string GetSqlDbTypeName(SqlDbType dbType)
        {
            switch (dbType)
            {
                case SqlDbType.UniqueIdentifier:
                    return "uniqueidentifier";
                case SqlDbType.NVarChar:
                    return "nvarchar";
                case SqlDbType.VarChar:
                    return "varchar";
                case SqlDbType.Char:
                    return "char";
                case SqlDbType.Int:
                    return "int";
                case SqlDbType.BigInt:
                    return "bigint";
                case SqlDbType.Bit:
                    return "bit";
                case SqlDbType.DateTime:
                    return "datetime";
                case SqlDbType.Decimal:
                    return "decimal";
                case SqlDbType.Float:
                    return "float";
                case SqlDbType.Text:
                    return "text";
                case SqlDbType.NText:
                    return "ntext";
                case SqlDbType.Binary:
                    return "binary";
                case SqlDbType.VarBinary:
                    return "varbinary";
                case SqlDbType.Image:
                    return "image";
            }
            throw new NotSupportedException();
        }

        public override Type GetDataType(SqlDbType dbType)
        {
            switch (dbType)
            {
                case SqlDbType.Char:
                case SqlDbType.VarChar:
                case SqlDbType.NVarChar:
                case SqlDbType.Text:
                case SqlDbType.NText:
                    return typeof(string);
                case SqlDbType.UniqueIdentifier:
                    return typeof(Guid);
                case SqlDbType.BigInt:
                    return typeof(long);
                case SqlDbType.Bit:
                    return typeof(bool);
                case SqlDbType.DateTime:
                    return typeof(DateTime);
                case SqlDbType.Image:
                case SqlDbType.Binary:
                case SqlDbType.VarBinary:
                    return typeof(byte[]);
                case SqlDbType.Int:
                    return typeof(int);
                case SqlDbType.Decimal:
                    return typeof(decimal);
            }
            throw new NotSupportedException();
        }

        string GenerateColumn(Column col)
        {
            StringBuilder sb = new StringBuilder();
            string dbTypeName = GetSqlDbTypeName(col.SqlDbType);
            sb.AppendFormat(
@"  [{0}] [{1}]", col.Name,dbTypeName);
            if (col.SqlDbType == SqlDbType.Char ||
                col.SqlDbType == SqlDbType.VarChar ||
                col.SqlDbType == SqlDbType.NVarChar)
            {
                sb.AppendFormat(" ({0})", col.Size);
            }
            if (col.IsAutocrement)
                sb.Append(" IDENTITY(1,1)");
            if (!col.IsNullable)
                sb.Append(" NOT NULL");
            return sb.ToString();
        }

        string GeneratePrimaryKeys(Table table)
        {
            List<Column> pks = new List<Column>();
            foreach (Column col in table.Columns)
                if (col.IsPrimaryKey)
                    pks.Add(col);
            if (pks.Count == 0)
                return "";
            Column lastCol = pks[pks.Count-1];
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(
@"  CONSTRAINT PK_{0} PRIMARY KEY
    (
        ",table.Name.Replace(" ",""));
            
            for (int i = 0; i < pks.Count - 1; i++) {
                Column col = pks[i];
                sb.AppendFormat("[{0}],", col.Name);
            }
            if (lastCol != null)
                sb.AppendFormat("[{0}]", lastCol.Name);
            sb.Append(@"
    )");
            return sb.ToString();
        }

        string GenerateTable(Table table)
        { 
            StringBuilder sb = new StringBuilder();
            Column lastCol = table.Columns.Count > 0 ? table.Columns[table.Columns.Count - 1] : null;
            sb.AppendFormat(
@"IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[{0}]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
",table.Name);
            sb.AppendFormat(
@"CREATE TABLE [dbo].[{0}]
(
",table.Name);
            //=========================
            for (int i=0;i<table.Columns.Count-1;i++) {
                Column col = table.Columns[i];
                sb.Append(GenerateColumn(col)+@",
");
            }
            if (lastCol != null)
                sb.Append(GenerateColumn(lastCol));
            string pk_contrains = GeneratePrimaryKeys(table);
            if (!string.IsNullOrEmpty(pk_contrains))
                sb.Append(@",
"+pk_contrains);
            //=========================
            sb.Append(
@"
)");
            sb.Append(
@"
END");
            return sb.ToString();
        }

        public override bool Exist(string tableName)
        {
            string cmdText =string.Format(
@"IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[{0}]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
	SELECT 1
  ELSE
	SELECT 0",tableName);
            object o = Provider.ExecuteScalar(cmdText);
            return Convert.ToInt32(o) == 1;
        }

        public override void Create(params Table[] tables)
        {
            if (tables.Length == 0)
                return;
            List<ExecuteCommand> cmds = new List<ExecuteCommand>();
            foreach (Table table in tables)
            {
                string cmdText = GenerateTable(table);
                ExecuteCommand cmd = new ExecuteCommand(cmdText);
                cmds.Add(cmd);
            }

            Provider.ExecuteTransaction(cmds.ToArray());
        }

        public override ExecuteCommand GetCreateCommand(Table table)
        {
            string cmdText = GenerateTable(table);
            ExecuteCommand cmd = new ExecuteCommand(cmdText);
            return cmd;
        }
    }
}
